SQLyog Help File

SQLyog is a easy to use, compact and very fast graphical tool to manage your MySQL database anywhere in the world.

This manual is available only in HTML format. We are planning to come out in other formats in future. Till then this document contains all the information you want to know about SQLyog. If you have any queries, questions or suggestion about SQLyog please mail them to support@webyog.com

SQLyog is SHAREWARE. You can you use it without any restrictions for 30 days. To buy SQLyog or to know about the pricing please visit the SQLyog Order Page.

Download the installer for SQLyog. Execute it and follow the steps to install the program.

Once you start SQLyog, the first thing you will get is Fig. 1. Fill in all the details about the server you want to connect i.e. host
address , user name, password and the port number. If you don't give any details SQLyog will try to connect to localhost with the default user as root and port number 3306. You must connect to a MySQL Server before you can perform any activities with the software. SQLyog's connection manager lets you to save unlimited Connection Details.

After you have connected to an instance of MySQL, you can make additional connections by selecting the New Connection option from the File menu.

The software has been tested in machines having memory ranging from 64 MB to 256 MB and processors ranging from 166 MHz to 1.4 GHz with OS
Win 95/98/XP/2000.

The title window shows the database name and the user name to current connection. If the query has been saved then it shows the complete path of the SQL file.

The window gives you complete details of the server you are connected to in a tree format. Apart form the database nodes, it has three nodes in the bottom, giving you details about Variables, Processlist and Status. Double-clicking on the node pops up the respective information. You can use the Processlist dialog box to kill any process except the thread on which you are connected.

Expanding database node will show the table(s) in the database and expanding the table name node will show the columns and indexes available for the table.

You can refresh the object browser with the latest detail about the server whenever you want by right clicking on the window and selecting Refresh Object Browser or by pressing F9.

When you right click on any object in the Object Browser, you will get a context sensitive popup menu. The options in the popup menu allows you to perform different operations on the selected object.

It is here where you write your SQL commands that you want to execute. There are various ways to enter SQL in the window -

The SQL window provides you with various commands to help you write SQL commands, including the standard editing commands : Undo, Cut, Copy, Paste and Select All. You can also force case, insert and remove comment marks.

If you are executing multiple queries then separate the individual queries by a (;). Each query would create a new tab for its resultset.

SQL window supports syntax highlighting for better usability. The different color signifies different element of SQL -

Note : If you are executing only one query then you don't have to end it with a (;). You can just execute it. Putting a (;) wont have any effect. SQLyog uses a (;) as a separator between queries.

This window shows you the resultset or messages returned by MySQL. It also shows you information about Databases and Tables and all the query(s) that has been executed in the current connection. The window has two permanent tabs - Object and History and two context specific tabs - Result and Message.

When you execute a query that returns a resultset, a new Result tab is added in the beginning of the tab window. If you have executed multiple queryies that produces multiple resultsets then you will have multiple Result tabs in the window with the resultsets of the respective query attached to it. Therefore, the first tab will hold the result of the first query and second for the second query and so on. To view the results just select the respective tab. SQLyog allows you to view results in two modes - Grid Mode and Text Mode. You can toggle between the two modes by pressing (Ctrl+L) or (Edit -> Show Results In Text). To switch between resulsets use the shortcut keys Alt+n where n specifies the resultset no.

The Message tab gives the information about the various query(s) executed i.e how many row(s) were returned/updated/modified and the time taken to execute the query(s) in microseconds, click here to see a sample of messages.

SQLyog logs all the query(s) executed by both the user and SQLyog itself. You can get the complete Query log in the History tab. SQLyog logs the time on which the query was executed and the time taken by the query to execute in microseconds (ms). This information is very useful for profiling and helps a person to learn SQL/MySQL by looking at query(s) that SQLyog uses to perform various tasks. see a sample of history log

Note : SQLyog does not stop executing the next query(s) if there is an error in one of the query(s). It will execute all the query(s) and then will show you the results. The tabs contain resultset of only the successful queries.

The status bar is divided into 5 parts with each part showing information about -

Keeping in mind about the preference of developers for keyboard, SQLyog incorporates many keyboard shortcuts for fast working. Following is the complete list of keyboard shortcuts available in SQLyog.

F5	Execute the current query.
F1	Show the help file.
F3	Keyboard Shortcut Window.
F6	Alter Table Structure.
F7	Manage Index Window.
F9	Refresh Object Browser.

Ctrl+1	Show / Hide Object Browser.
Ctrl+2	Show/Hide Result Window.
Ctrl+B	Set Focus On Object Browser.
Ctrl+E	Set Focus On SQL Window.
Ctrl+D	Create Database.
Ctrl+F	Find.
Ctrl+H	Replace.
Ctrl+L	Toggle Result In Text/Grid Mode.
Ctrl+M	Create A New Connection.
Ctrl+N	Create A New Connection With Current Connection Properties.
Ctrl+O	Open A SQL Document In SQL Window.
Ctrl+R	Set Focus On Result Window.
Ctrl+S	Save The Current SQL Document.
Ctrl+U	Add A New User.
Ctrl+V	Paste Data Into SQL Window.
Ctrl+F4	Disconnect The Current Connection.
Ctrl+F5	Execute Selected Query(s)

Shift+F5	Execute All Query(s).
Shift+Del	Empty A Table.

Ctrl+Alt+C	Export Table Data As CSV.
Ctrl+Alt+E	Export Data As Batch Scripts.
Ctrl+Alt+T	Table Diagnostics.
Ctrl+Alt+U	Edit User Permissions.
Ctrl+Alt+F	Flush Dialog Box

Ctrl+Shift+C	Comment Selection In SQL Window.
Ctrl+Shift+E	Export Resultset.
Ctrl+Shift+F	Add Current SQL To Personal Folder.
Ctrl+Shift+L	Make Selection Lowercase.
Ctrl+Shift+P	Activate The Personal Folder Menu.
Ctrl+Shift+R	Remove Comment From Selection.
Ctrl+Shift+S	Open Create Schema Dialog.
Ctrl+Shift+T	Open Insert Template Dialog.
Ctrl+Shift+U	Make Selection Uppercase.
Ctrl+Shift+M	Import CSV Data To The Table.
Ctrl+Shift+Q	Execute A SQL Batch File.
Ctrl+Shift+W	Open Manage User Dialog Box.

Alt+1...n..9	Select Nth Tab In Result Window.

You can get the above list by selecting Help / Keyboard Shortcuts in SQLyog or by pressing F3.

Note : The feature will work only with characters. Any special character wont be affected.

Note : Even if you don't select the whole text in a line, the line will be commented out. So you can only comment or uncomment the whole line with this option. To comment multiple line, select multiple lines.

SQLyog gives you the most easy and elegant to bring two databases into complete sync i.e. creating new tables, dropping tables, altering table structure etc. with a single mouse click. Press Ctrl+Q or select Tools->Schema Synchronization Tool to start the Database Synchronization Tool. Select the two database which you want to bring in Sync and select one of the command buttons in the bottom. Click here to see a screenshot of database sync tool dialog

SQLyog Job Agent ( SJA ) is a high-performance, multithreaded, multi platform application to automate and schedule Synchronization of Data between two MySQL hosts. On Windows, it is included with SQLyog 3.63 - the latest version of the award winning MySQL Client. The Linux version of SJA is FREE and can be downloaded from http://www.webyog.com. In SQLyog, it can be accessed through Tools->Database Synchronization Wizard.

SJA does not require any installation at hosts running the MySQL server. You can use any host to run the SJA. For example – you can use SJA to keep your production databases ( probably hosted with an ISP ) in complete sync with your test database located in your PC or LAN.

SJA uses an efficient algorithm to generate checksums to find out the changes. Therefore, only those rows that have been inserted, updated or deleted since the last sync are transferred between the hosts.

Additionally, you can configure SJA to detect changes only for specific rows and columns. For example – you can exclude blob columns or include only those rows that fulfill a WHERE clause. This makes is an ideal tool to sync data even if there is limited bandwidth.

SJA is a command line tool that accepts a Job Definition file encoded in XML as one of the parameters. You can either create the Job Definition file manually or use one of the wizards included with SQLyog. If you use SQLyog to create your job files, you don’t need to have any knowledge about XML or the Job Definition schema.

On Windows platforms, SQLyog uses the Task Scheduler services to schedule your jobs. On other platforms you can use the respective OS scheduling services, e.g – on Linux, you can use cron to schedule jobs.

Select DB -> Create Database. Give the database name and press the Create button.

Select the database that you want to drop in the Object Browser and select Drop Database from the popup menu ( or press Delete ).

Empty a database allows you to quickly drop all the tables from a database. To use this option select Empty Database from the popup menu ( or press Shift+Delete).

You can copy a database with all its table structure as well as the data to another database which can be located in another host. To copy a database, select the DB you want to copy and select Copy Database... from the popup menu. The copy database dialog will give you a list of all the host you are connected to in the session. Select a database in which you want to copy the data and press Copy.

Using SQLyog you can quickly create schema of your database in HTML. Select the database in the Object Browser and select Create Database Schema In HTML from the popup menu.

Select the table(s) for which you want to create the schema in the dialog and press Create.

SQLyog provides you with a very easy to use Excel like Grid to Create Table(s). Select a database in the Object browser and select the menu option (Create Table In The Database from the popup menu) or (DB -> Create Table In The Database) or (Press INS if you have focus on Object Browser). Insert your values in the Grid and press Create Table to provide the name of the Table and you are done. You can also specify other Table properties ( e.g. Table Handler, Comments etc.) during the creation time.

You can edit an existing table's structure using SQLyog's easy to use Table Editor. Just select a Table Object in the Object Browser and select the menu option (Edit Table Structure from the popup menu) or (Edit -> Edit Table Structure) or (Press F6 if you have focus on Object Browser). You get a Excel grid just like one you get for creating table with the column names and values already filled up. Edit column value, add new column(s), delete existing column(s) etc. and press Alter Table.

You can quickly Insert/Update data in a table by using SQLyog's Insert/Update Data tool. To use the option, select a Table Object in the Object Browser and select the menu option (Insert/Update Data from the data) or (Edit -> Insert/Update data) or (Press Ctrl+Enter when you have focus on the Object Browser). You can use this tool to quickly insert/update data on a table. You can also insert/modify a Blob Value. Just change the values in the Grid that you want to change or Insert new values and press OK to make the changes. While editing if you move Up/Down then the changes are done instantly.If you are inserting a row that has an AutoIncrement column then it would automatically fill the column with the new ID if you don't provide one.

By default, SQLyog returns you the top 5000 rows but you can change the limit by changing the limits in the top of the dialog. This detail is stored in sqlyog.ini so whenever you use the option you will get the data filtered to the last limit.

SQLyog provides you with the facility to update a set of data which is returned by a query. Instead of executing the query by pressing F5, execute the query using F8. A Grid will come up with the result which you can update. This allows you to edit a Resultset having a complex WHERE clause or JOINS. SQLyog will generate the corresponding Insert/Update script to commit the changes.

Note: To update the data, all the Primary Key field and all Not Nullable field without having a Default Value of a table must be selected in the result.

SQLyog allows you to export your resultset in three formats - XML, HTML, CSV. To export a resultset, select the tab whose Result you want to export and select Export Resultset from the popup. Select the format, provide the filename and press the Export button. Click Here To View Export Resultset Dialog.

SQLyog allows you keep your frequently used SQL scripts in a Personal Folder. To save a SQL query press Ctrl+Shift+F and provide a name. When you want it back, press Ctrl+Shift+P and select the query name from the popup menu. SQLyog allows you to save upto 99 SQL queries in the personal folder. Click Here View Personal Folder Menu.

You can manage your Personal Folder by selecting Edit/Manage Personal Folder or by selecting Manage Personal Folder from the context menu of the SQL window.

Check the keys that you want to delete and press Delete. The files will be deleted and the Personal Folder Menu will be updated accordingly.

SQLyog ODBC Import Wizard lets you extract, transform, and consolidate data from disparate sources into MySQL in very simple and efficient way.

SQLyog has probably the most powerful and comprehensive ODBC Import functionality through which you can import data from any ODBC compliant datasource. To import a data just create a DSN to the database and select ODBC Import Wizard by pressing Ctrl+Alt+O or Tools-> ODBC Import Wizard. Select the correct options in the wizard and in a flash all your data will be imported. Click Here To View An Image of ODBC Import Wizard

Using SQLyog you can perform various operations on table. SQLyog provides you with the following options -

SQLyog provides you with a comprehensive User Manager through which you can add user, edit users privileges and manage passwords. It also allows you to give Database, Table and Column level privileges to a user.

Select Tools -> User Manager -> Add Users, give the name, host, password and the select the privileges that you want to give to user. Through this option you can provide Global Privileges to the user. Therefore, if you check Select privilege for a user, the user has Select privilege for all the database & tables in the MySQL Server.

To give database, table, column level privileges use Tools -> User Manager -> Manage Permissions, that is described in detail later in this document.

Select Tools -> User Manager -> Edit Users. Through this option you can changes a User's Name, Host and Password. You can also modify the users Global Privileges.

To give database, table, column level privileges use Tools -> User Manager -> Manage Permissions, that is described in detail later in this document.

SQLyog's comprehensive User Manager allows you to give Database, Table and Column level privilege to a user.

Select Tools -> User Manager -> Manage Permissions to give grants to a user on a specific database or table. Select the respective database or the table in the grid and check the Privileges that you want to give to the user.

Note : To give privileges to a particular column you should not give the same privilege to its corresponding table otherwise the user will have the privilege for the whole table making the column privilege useless.

You can create/edit indexes in a table by using SQLyog's Index Manager. To view the existing Indexes in a table, select the table in Object Browser and select Manage Indexes from the popup menu.

To create a new Index press the New button. You will see the Index Manager dialog. Check the column(s) on which you want to create the index, also you can change its order by using Up/Down button. After you are finished press the Apply Button.

To edit an existing select the Index and press the Edit button. You will see the Index Manager dialog. Check/Uncheck the column(s) on which you want to create the index, also you can change its order by using Up/Down button. After you are finished press the Apply Button.

SQLyog provides you with an easy Relationship Manager. Just select the Key Table and press F10 or select Manage Relationships from the popup menu. You can create a new relationship or delete an existing relationships with just a few mouseclicks. click here to see a screeshot of manage realtionships dialog

To flush Host / Logs / Privileges / Tables / Tables With Read Lock / Status / All, select the appropriate option from the Flush dialog box in Tools menu.

To run a quick check on your database and table(s), you can use SQLyog Table Diagnostics option.

In the dialog box select the database and the table that you want to diagnose and select the type of diagnosis you want to do on the table.

	Signifies tablename, database name etc. Word with this color means its not a reserved word in MySQL.
	Signifies its a keyword in MySQL. E.g. select, update, insert etc.
	Signifies its a function provided by MySQL. E.g. ifnull() etc.
	Signifies the word is part of comment. More about comment.
	Signifies its a string value.
	Signifies its a operator. Eg. =,+,-, etc.